﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading;
using System.Windows.Forms;
using Microsoft.VisualBasic;
using modBusTest;
using Modbus.Device;
using System.Data.SQLite;
using System.Data.Common;
using ModbusTestTool.Error_Logging;
using Microsoft.Office.Interop.Excel;
using System.Reflection;

namespace ModbusTestTool
{
    class SqlCommunication
    {
        //for sql stuff
        SQLiteConnection conn;
        SQLiteCommand cmd;
        //this variables holds the name of the data table
        string table_data;
        string table_key; 
        const string fileIndexTableName = "FileIndexTable";


        public SqlCommunication(string inputFileName, modbusmap internalMap)
        {
            HandleCreatingSqlTables(inputFileName, internalMap);
        }


        /// <summary>
        /// An index table to map table name to xml file path
        /// </summary>
        /// <param name="filepath"></param>
        private void CreateIndexTable(string filepath, string table_name)
        {

            try
            {
                if (!DoesTableExist(fileIndexTableName))
                {
                    cmd.CommandText = string.Format("CREATE TABLE {0} (table_name TEXT, table_path TEXT, table_file BLOB);", fileIndexTableName);
                    cmd.ExecuteNonQuery();
                }

                // check to see if the path alread exists

                cmd.CommandText = string.Format("select * from {0} WHERE table_name = \"{1}\";", fileIndexTableName, table_name);
                SQLiteDataReader read = cmd.ExecuteReader();


                if (!read.HasRows)
                {
                    read.Close();
                    //cmd.CommandText = string.Format("INSERT INTO {0} VALUES (\"{1}\",\"{2}\");", fileIndexTableName, table_name, filepath);
                    cmd.CommandText = string.Format("INSERT INTO {0} VALUES (@table_name,@filePath,@file_content);", fileIndexTableName, table_name, filepath);
                    cmd.Parameters.Add(new SQLiteParameter("@table_name", DbType.String));
                    cmd.Parameters.Add(new SQLiteParameter("@filePath", DbType.String));
                    cmd.Parameters["@table_name"].Value = table_name;
                    cmd.Parameters["@filePath"].Value = filepath;
                    StreamReader readHandle = new StreamReader(File.Open(filepath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite));
                    string temp = readHandle.ReadToEnd();


                    cmd.Parameters.AddWithValue("@file_content", temp);
                    cmd.ExecuteNonQuery();
                }
                else
                {
                    read.Close();
                }
            }
            catch (Exception err)
            {
                ErrorLog.Instance.addMessage(err.Message);
            }



        }

        private void HandleCreatingSqlTables(string inputFileName,modbusmap internalModbusMap )
        {
            if (!File.Exists(@"nk.db"))
                SQLiteConnection.CreateFile("nk.db");

            try
            {

                // conn = new SQLiteConnection("Data Source=nk.db");
                conn = new SQLiteConnection("Data Source=nk.db;Version=3;Compress=True;");

                var items = from n in internalModbusMap.storage select n.paramName;
                conn.Open();

                //this table has the key list
                cmd = new SQLiteCommand(conn);


                string hash = Crc32.ComputeHashString(inputFileName);

                //name of table we plan to put in the data base
                table_key = inputFileName.Split('\\').Last().Split('.')[0] + "_key_" + hash;
                table_data = inputFileName.Split('\\').Last().Split('.')[0] + "_data_" + hash;


                //check to see if this table exists
                bool doesTableExist = DoesTableExist(table_key);


                CreateIndexTable(inputFileName, table_data);


                //create table for the index
                if (!doesTableExist)
                {
                    cmd.CommandText = string.Format("CREATE TABLE {0} (Key_Name TEXT);", table_key);
                    cmd.ExecuteNonQuery();

                    cmd.CommandText = string.Format("INSERT INTO {0} VALUES (\"Date\");", table_key);
                    cmd.ExecuteNonQuery();


                    //using a trasaction to put each key into the table
                    SQLiteTransaction temp = conn.BeginTransaction();
                    foreach (BaseRegister item in internalModbusMap.storage)
                    {
                        cmd.CommandText = string.Format("INSERT INTO {0} VALUES (\"{1}\");", table_key, item.paramName);
                        cmd.ExecuteNonQuery();
                    }
                    temp.Commit();

                    ((IDisposable)temp).Dispose();

                    //creating columns for all the different parameters
                    string[] keys = new string[internalModbusMap.storage.Count + 1];

                    keys[0] = "I0 TEXT";
                    for (int i = 1; i < keys.Length; i++)
                    {
                        keys[i] = "I" + i.ToString() + " REAL";
                    }

                    // do the sql statement to create the table

                    cmd.CommandText = string.Format("CREATE TABLE {0} ({1});", table_data, string.Join(",", keys));
                    cmd.ExecuteNonQuery();

                }
            }
            catch (Exception err)
            {
                MessageBox.Show(err.Message);
            }


            //cmd.CommandText = "CREATE TABLE maps (key CHAR, 


            //MessageBox.Show("Created DataBase");
        }


        private bool DoesTableExist(string table_key)
        {
            bool doesTableExist = false;
            cmd.CommandText = "SELECT name FROM sqlite_master;";
            SQLiteDataReader dataRead = cmd.ExecuteReader();

            while (dataRead.Read())
            {
                if (dataRead.GetString(0) == table_key)
                {
                    doesTableExist = true;
                    break;
                }
            }

            dataRead.Close();
            return doesTableExist;
        }

        public void AddRow(IEnumerable<string> items)
        {
            cmd.CommandText = string.Format("INSERT INTO {0} VALUES ({1});", table_data, String.Format("\"{0}\",", DateAndTime.Now.ToString()) + string.Join(",", items));
            cmd.ExecuteNonQuery();
        }

        public void clearDataBase()
        {
           
            cmd.CommandText = string.Format("DELETE FROM {0};", table_data);
            cmd.ExecuteNonQuery();

        }

        /// <summary>
        /// This function should take contents of table and dump it to a file
        /// </summary>
        /// <param name="filename"></param>
        public void dumpDataBase(string filename)
        {
            //conn.Open();
            SQLiteDataAdapter adapt = new SQLiteDataAdapter("SELECT * FROM " + table_data, conn);
            System.Data.DataTable dt_data = new System.Data.DataTable(table_data);
            System.Data.DataTable dt_key = new System.Data.DataTable(table_key);
            adapt.Fill(dt_data);
            adapt = new SQLiteDataAdapter("SELECT * FROM " + table_key, conn);
            adapt.Fill(dt_key);

            

            StringWriter temp = new StringWriter();
            dt_data.WriteXml(temp);
            string full = temp.ToString();
            for (int i = 0; i < dt_key.Rows.Count; i++)
            {

                //full = full.Replace("I" + i, "\"" + dt_key.Rows[i][0].ToString() + "\"");
                //dt_data.Columns[i].ColumnName = dt_key.Rows[i][0].ToString() + " " + i;
            }

            using (StreamWriter outHandle = new StreamWriter(filename))
            {
                outHandle.Write(full);
            }
            
        }

        public void dumpDataBaseExcel(string filename)
        {
            //conn.Open();
            SQLiteDataAdapter adapt = new SQLiteDataAdapter("SELECT * FROM " + table_data, conn);
            System.Data.DataTable dt_data = new System.Data.DataTable(table_data);
            System.Data.DataTable dt_key = new System.Data.DataTable(table_key);
            adapt.Fill(dt_data);
            adapt = new SQLiteDataAdapter("SELECT * FROM " + table_key, conn);
            adapt.Fill(dt_key);

            Microsoft.Office.Interop.Excel.Application test = new Microsoft.Office.Interop.Excel.Application();

            test.Visible = true;
            
            Workbook book = test.Workbooks.Add(Missing.Value);

            Worksheet sheet = book.ActiveSheet as Worksheet;
            sheet.Name = "Output";

            // put in header
            for (int i = 1; i < dt_key.Rows.Count+1; i++)
            {
                sheet.Cells[1, i] = dt_key.Rows[i-1][0].ToString();
            }

            //put in the rest of the data
            // need to add count + 1 since excel uses 1 numbering and dt is 0 based
            for (int i = 1; i < dt_data.Rows.Count+1; i++)
            {
                for (int j = 1; j < dt_data.Columns.Count+1; j++)
                {   //i+1 to skip the header row
                    sheet.Cells[i+1, j] = dt_data.Rows[i-1][j-1].ToString();

                }
            }
            try
            {
                book.SaveAs("test.xlsx", XlFileFormat.xlOpenXMLWorkbook,
                    Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                    XlSaveAsAccessMode.xlExclusive,
                    Missing.Value, Missing.Value, Missing.Value,
                    Missing.Value, Missing.Value);

                book.Close(Missing.Value, Missing.Value, Missing.Value);
                book = null;
                test.Quit();
            }
            catch (Exception err)
            {
                MessageBox.Show("Something went wrong in excel save; do it manually\r\n Err: "+err.Message);
            }
            GC.WaitForPendingFinalizers();
            GC.Collect();
            GC.WaitForPendingFinalizers();
            GC.Collect();

        }
   
    
    }
}
